"""
    实现统计报表
"""

from openpyxl import load_workbook
from openpyxl.chart import (
    AreaChart,
    Reference,
    Series,
)
import pymysql

class GaoKaoExport(object):

    # 构造函数
    def __init__(self):
        # 初始化
        self.wb = load_workbook('./static/tmpl.xlsx')
        self.ws = self.wb.active
        self.ws.title = '成绩统计'
        self.ws.sheet_properties.tabColor = 'ff0000'


    # 获取 mysql 的连接
    def get_conn(self):
        # 连接数据库
        try:
            db = pymysql.Connect(
                host='localhost',
                port=3306,
                user='root',
                passwd='wjw1996321..',
                db='grade',
                charset='utf8'
            )
            return db
        except:
            print('数据库连接异常')


    # 导出数据
    def export_data(self):
        # 数据库操作
        db = self.get_conn()  # 获取数据库
        cur = db.cursor()  # 获取游标
        SQL = 'SELECT `year`, `max`, `avg` FROM `user_grade`'  # 需要执行的SQL语句(如果数据量大，需要借助分页)
        cur.execute(SQL)  # 执行SQL语句
        rows = cur.fetchall()  # 获取查询的值

        # 写入数据
        row_id = 10
        for (i, row) in enumerate(rows):
            # 高级方式
            (self.ws['C{0}'.format(row_id)], self.ws['D{0}'.format(row_id)], self.ws['E{0}'.format(row_id)]) = row
            row_id += 1

        # 绘制图表
        chart = AreaChart()
        chart.title = "统计表"
        chart.style = 13
        chart.x_axis.title = '年份'
        chart.y_axis.title = '分数'
        # 横坐标
        cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
        # 数据区域
        data = Reference(self.ws, min_col=4, min_row=9, max_col=5, max_row=row_id)
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(cats)

        self.ws.add_chart(chart, "A{0}".format(row_id + 2))

        # 保存Excel
        self.wb.save('./static/stats.xlsx')
        # 关闭连接
        db.close()




if __name__ == '__main__':
    client = GaoKaoExport()
    client.export_data()